﻿namespace CRM.DataAccess.DAOs
{
    using System.Collections.Generic;
    using System.Data.Entity;
    using CRM.DataAccess.Entities;
    using System.Linq;
    using System.Data.Entity.Validation;
    using System.Diagnostics;
    using System;

    public class ProductDAO : DbContext
    {
        public ProductDAO()
            : base("name=Entities")
        {
        }
        public DbSet<PRODUCT> PRODUCTS { get; set; }
        public DbSet<PRODUCT_DETAIL> PRODUCT_DETAIL { get; set; }
        public DbSet<PRODUCT_CATEGORY> PRODUCT_CATEGORY { get; set; }
        public DbSet<PRODUCT_MANUFACTURER> PRODUCT_MANUFACTURER { get; set; }
        public DbSet<PRODUCT_USAGE_UNIT> PRODUCT_USAGE_UNIT { get; set; }
        public DbSet<USER> USERS { get; set; }

        //Get all product
        public List<PRODUCT> GetAllProduct(long? assignedToID)
        {
            try
            {
                var Query = (from p in PRODUCTS
                             where !p.DELETED && (p.ASSIGN_TO == (assignedToID == null ? p.ASSIGN_TO : assignedToID))
                             select p);
                List<PRODUCT> re = Query.ToList();
                if (re.Count() > 0)
                    return re;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get top 1 product
        public PRODUCT GetTop1Product(long? assignedToID)
        {
            try
            {
                var Query = (from p in PRODUCTS
                             where !p.DELETED && (p.ASSIGN_TO == (assignedToID == null ? p.ASSIGN_TO : assignedToID))
                             select p).FirstOrDefault<PRODUCT>();
                PRODUCT re = Query as PRODUCT;
                return re;
            }
            catch
            {
                return null;
            }
        }
        
        //Get Usage Unit
        public List<PRODUCT_USAGE_UNIT> GetProductUsageUnit()
        {
            try
            {
                var Query = from p in PRODUCT_USAGE_UNIT
                            select p;
                List<PRODUCT_USAGE_UNIT> ms = Query.ToList();
                if (ms.Count > 0)
                    return ms;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Category
        public List<PRODUCT_CATEGORY> GetProductCategory()
        {
            try
            {
                var Query = from p in PRODUCT_CATEGORY
                            select p;
                List<PRODUCT_CATEGORY> ms = Query.ToList();
                if (ms.Count > 0)
                    return ms;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Manufacturer
        public List<PRODUCT_MANUFACTURER> GetProductManufacturer()
        {
            try
            {
                var Query = from p in PRODUCT_MANUFACTURER
                            select p;
                List<PRODUCT_MANUFACTURER> ms = Query.ToList();
                if (ms.Count > 0)
                    return ms;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get product by ProductID
        public PRODUCT GetProductByID(long id)
        {
            try
            {
                var Query = from p in PRODUCTS
                            where p.ID == id
                            select p;
                List<PRODUCT> re = Query.ToList();
                if (re != null)
                    return re[0];
                return null;
            }
            catch
            {
                return null;
            }
        }

        //Get product detail by ID
        public PRODUCT_DETAIL GetProductDetailByID(long id)
        {
            try
            {
                var Query = from p in PRODUCT_DETAIL
                            where p.ID == id
                            select p;
                List<PRODUCT_DETAIL> pds = Query.ToList();
                if (pds != null)
                    return pds[0];
                return null;
            }
            catch
            {
                return null;
            }
        }

        //Get Product by Condition
        public List<PRODUCT_DETAIL> GetProductByCondition(DateTime? dateFrom, DateTime? dateTo, string productName, string partNumber, double? unitPrice, long? assignTo)
        {
            try
            {
                var Query = from p in PRODUCT_DETAIL
                            where p.ASSIGN_TO_ID == (assignTo == null ? p.ASSIGN_TO_ID : assignTo)
                            && (productName != "" ? (p.PRODUCT_NAME.ToUpper().Contains(productName.ToUpper())) : (true))
                            && (partNumber != "" ? (p.PART_NUMBER.ToUpper().Contains(partNumber.ToUpper())) : (true))
                         //       && p.PRODUCT_NAME.ToUpper() == (productName == "" ? p.PRODUCT_NAME.ToUpper() : productName.ToUpper())
                        //        && p.PART_NUMBER.ToUpper() == (partNumber == "" ? p.PART_NUMBER.ToUpper() : partNumber.ToUpper())
                                && p.UNIT_PRICE == (unitPrice == null ? p.UNIT_PRICE : unitPrice)
                                && (p.DATE_ENTERED >= dateFrom && p.DATE_ENTERED < dateTo)
                            select p;
                List<PRODUCT_DETAIL> products = Query.ToList();
                if (products.Count > 0)
                    return products;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Delete product
        public int DeleteProduct(long proID)
        {
            try
            {
                PRODUCTS.RemoveRange(PRODUCTS.Where(x => x.ID == proID));
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Add new Product
        public int AddNewProduct(PRODUCT product)
        {
            try
            {
                this.PRODUCTS.Add(product);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Update Product
        public int UpdateProduct(PRODUCT pro)
        {
            try
            {
                var original = PRODUCTS.Find(pro.ID);
                if (original != null)
                {
                    Entry(original).CurrentValues.SetValues(pro);
                    SaveChanges();
                    return 1;
                }
                return -1;
            }
            catch (DbEntityValidationException dbEx)
            {
                foreach (var validationErrors in dbEx.EntityValidationErrors)
                {
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                        Trace.TraceInformation("Property: {0} Error: {1}",
                                                validationError.PropertyName,
                                                validationError.ErrorMessage);
                    }
                }
                return -1;
            }
        }
        // add new Manufacturer / Supplier
        public int AddNewSupplier(PRODUCT_MANUFACTURER proM)
        {
            try
            {
                this.PRODUCT_MANUFACTURER.Add(proM);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
    }
}
